I've got a table where one of the main lookup access is by email address, which is generally a case insensitive lookup.
With the constraints that: some of the entries aren't reall email addresses and I can't change the case of all the data
becauseof that:
Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I get
thebenefit of an index?
In the following queries, there's an index on dl_profile(_email).
test=# explain analyze select _donorNum from dl_profile where _email~'^foo@bar.org$' ;
NOTICE: QUERY PLAN:
Index Scan using dl_profile_email on dl_profile (cost=0.00..467.75 rows=1 width=4) (actual time=14.59..14.63 rows=1
loops=1)
Total runtime: 14.97 msec
EXPLAIN
test=# explain analyze select _donorNum from dl_profile where _email~*'^foo@bar.org$' ;
NOTICE: QUERY PLAN:
Seq Scan on dl_profile (cost=0.00..10607.28 rows=1 width=4) (actual time=4196.43..5078.86 rows=1 loops=1)
Total runtime: 5079.42 msec
thanks
eric
